using System.Data;
using Newtonsoft.Json;
using OfficeOpenXml;

namespace Tools.ExcelToJson.Editor
{
    // ReSharper disable once InconsistentNaming
    internal static class EPPlusExtensions
    {
        public static string ExcelSheetToJson(this ExcelWorksheet ws, bool hasHeader)
        {
            var dataTable = new DataTable();
            foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
            {
                if (!string.IsNullOrEmpty(firstRowCell.Text))
                {
                    var firstColumn = $"Column {firstRowCell.Start.Column}";
                    dataTable.Columns.Add(hasHeader ? firstRowCell.Text : firstColumn);
                }
            }

            var startRow = hasHeader ? 2 : 1;
            for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
            {
                var wsRow = ws.Cells[rowNum, 1, rowNum, dataTable.Columns.Count];
                
                var hasData = false;
                foreach (var cell in wsRow)
                {
                    if (!string.IsNullOrEmpty(cell.Text))
                        hasData = true;
                }

                if (hasData)
                {
                    var row = dataTable.Rows.Add();
                    foreach (var cell in wsRow) 
                        row[cell.Start.Column - 1] = cell.Text;   
                }
            }

            var result = JsonConvert.SerializeObject(dataTable, Formatting.Indented);
            return result;
        }
    }
}